﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Text;
using System.Net;

namespace BZZ
{
    #region 数据操作类
    public class DataAcess
    {

        SqlCommand _command = new SqlCommand();

        #region init
        public DataAcess()
        {
            _command.Parameters.Clear();
            PublicConst pc = new PublicConst();
            ConnectStr = pc.getConnstr();
        }

        /// <summary>
        /// 需要连接默认连接串之外的数据库时候使用
        /// </summary>
        /// <param name="connstrName">config中connstr名称</param>
        public DataAcess(string connstrName)
        {
            _command.Parameters.Clear();
            PublicConst pc = new PublicConst();
            ConnectStr = pc.getConnstr(connstrName);
        }
        #endregion

        #region execute
        /// <summary>
        /// 执行sql，返回dataset
        /// </summary>
        public DataSet Execute(string strSql)
        {
            #region Execute
            SqlConnection con = new SqlConnection();
            DataSet dataSet = null;
            try
            {
                con.ConnectionString = _conString;
                //con.Open();
                _command.Connection = con;
                _command.CommandTimeout = 1800;
                _command.CommandText = strSql;
                SqlDataAdapter adapter = new SqlDataAdapter(_command);
                dataSet = new DataSet();
                adapter.Fill(dataSet);
            }
            catch (System.Exception ex)
            {
                dataSet = null;
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return dataSet;
            #endregion
        }
        /// <summary>
        /// 执行sql，返回dataset,参数化变量
        /// </summary>
        public DataSet Execute(string strSql, params SqlParameter[] cmdParms)
        {
            #region Execute
            SqlConnection con = new SqlConnection();
            DataSet dataSet = null;
            try
            {
                _command.Parameters.Clear();
                con.ConnectionString = _conString;
                //con.Open();
                _command.CommandTimeout = 1800;
                PrepareCommand(_command, con, null, strSql, cmdParms);
                SqlDataAdapter adapter = new SqlDataAdapter(_command);
                dataSet = new DataSet();
                adapter.Fill(dataSet);
            }
            catch (System.Exception ex)
            {
                dataSet = null;
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return dataSet;
            #endregion
        }

        /// <summary>
        /// 执行sql，返回dataset,list形式参数化变量
        /// </summary>
        public DataSet Execute(string strSql, List<SqlParameter> paraList)
        {

            return Execute(strSql, paraList == null ? null : paraList.ToArray());
        }

        /// <summary>
        /// 根据页码获取当前一页的数据
        /// 例子：ExecutePageSql(1,10,"status=1","dt desc",null,"id,title,content,dt,status","news"
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        /// <param name="tblName">表名，支持多表</param>
        /// <returns></returns>
        public DataSet ExecutePageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields, string tblName)
        {
            if (currentPage <= 0) currentPage = 1;
            if (pageSize < 1) pageSize = 1;
            int startRow = (currentPage - 1) * pageSize;
            int endRow = currentPage * pageSize;

            if (dataFields == "") dataFields = "*";
            StringBuilder sb = new StringBuilder();
            sb.Append("select " + dataFields + " from ");
            sb.Append("(select Row_number() over(order by " + strOrderBy + " ) as IDRank," + dataFields + " from " + tblName + " ");
            if (strWhere != "") sb.Append(" where " + strWhere);
            sb.Append(" ) as IDWithRowNumber ");
            sb.Append("where IDRank>" + startRow.ToString() + " and IDRank<=" + endRow.ToString());

            #region 另一分页方案
            //下面这种方法，在大数据量中或许更优，但是对于多表查询，反而更繁琐，暂时不采用 -- buzhizhe注。
            //sb.Append("select a.* from ");
            //sb.Append(tblName);
            //sb.Append(" as a inner join ((select id,Row_number() over(order by " + strOrderBy + " ) as IDRank from " + tblName + " ");
            //if (strWhere != "") sb.Append(" where " + strWhere);
            //sb.Append(" ) as IDWithRowNumber ");
            //sb.Append("where IDRank>" + startRow.ToString() + " and IDRank<=" + endRow.ToString());
            //sb.Append(") as b on a.id=b.id order by b.IDRank"); 
            #endregion

            return Execute(sb.ToString(), cmdParms);

        }


        #endregion

        #region ExecuteOneRow
        /// <summary>
        /// 执行sql，返回dataset中第一行
        /// </summary>
        public DataRow ExecuteOneRow(string strSql)
        {
            #region ExecuteOneRow
            DataSet dset = Execute(strSql);
            DataRow dr = null;
            if (dset.Tables[0].Rows.Count > 0) dr = dset.Tables[0].Rows[0];
            return dr;
            #endregion
        }
        /// <summary>
        /// 执行sql，返回dataset中第一行,参数化变量
        /// </summary>
        public DataRow ExecuteOneRow(string strSql, params SqlParameter[] cmdParms)
        {
            #region ExecuteOneRow
            DataSet dset = Execute(strSql, cmdParms);
            DataRow dr = null;
            if (dset.Tables[0].Rows.Count > 0) dr = dset.Tables[0].Rows[0];
            return dr;
            #endregion
        }

        /// <summary>
        /// 执行sql，返回dataset中第一行,list形式参数化变量
        /// </summary>
        public DataRow ExecuteOneRow(string strSql, List<SqlParameter> paraList)
        {
            return ExecuteOneRow(strSql, paraList == null ? null : paraList.ToArray());
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 返回结果集中的第一行第一列数据，忽略其他行或列
        /// </summary>
        public string ExecuteScalar(string strSql)
        {
            SqlConnection con = new SqlConnection();
            string RowValue = "None";
            try
            {
                con.ConnectionString = _conString;
                con.Open();
                _command.CommandText = strSql;
                _command.Connection = con;
                RowValue = _command.ExecuteScalar().ToString();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return RowValue;
        }
        /// <summary>
        /// 返回结果集中的第一行第一列数据，忽略其他行或列
        /// </summary>
        public string ExecuteScalar(string strSql, params SqlParameter[] cmdParms)
        {
            SqlConnection con = new SqlConnection();
            string RowValue = "None";
            try
            {
                con.ConnectionString = _conString;
                con.Open();
                PrepareCommand(_command, con, null, strSql, cmdParms);
                RowValue = _command.ExecuteScalar().ToString();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return RowValue;
        }
        /// <summary>
        /// 返回结果集中的第一行第一列数据，忽略其他行或列
        /// </summary>
        public string ExecuteScalar(string strSql, List<SqlParameter> paraList)
        {
            return ExecuteScalar(strSql, paraList == null ? null : paraList.ToArray());
        }
        #endregion

        #region ExecuteCount
        /// <summary>
        /// 返回结果集中的第一行第一列数据，忽略其他行或列
        /// </summary>
        public int ExecuteCount(string strSql)
        {
            int rtn = -1;
            try { rtn = Convert.ToInt32(ExecuteScalar(strSql)); }
            catch { }
            return rtn;
        }
        /// <summary>
        /// 返回结果集中的第一行第一列数据，忽略其他行或列
        /// </summary>
        public int ExecuteCount(string strSql, params SqlParameter[] cmdParms)
        {
            int rtn = -1;
            //try { 
            rtn = Convert.ToInt32(ExecuteScalar(strSql, cmdParms));
            //}
            //catch { }
            return rtn;
        }
        /// <summary>
        /// 返回结果集中的第一行第一列数据，忽略其他行或列
        /// </summary>
        public int ExecuteCount(string strSql, List<SqlParameter> paraList)
        {
            return ExecuteCount(strSql, paraList == null ? null : paraList.ToArray());
        }
        #endregion

        #region executeNonQuery
        /// <summary>
        /// 直接SQL语句并返回受影响的行数
        /// </summary>
        public int ExecuteNonQuery(string strSql)
        {
            _command.Parameters.Clear();
            SqlConnection con = new SqlConnection();
            int RowCount = -1;
            try
            {
                con.ConnectionString = _conString;
                con.Open();
                _command.CommandText = strSql;
                _command.Connection = con;
                RowCount = _command.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return RowCount;
        }

        /// <summary>
        /// 直接SQL语句并返回受影响的行数
        /// </summary>
        public int ExecuteNonQuery(string strSql, params SqlParameter[] cmdParms)
        {
            _command.Parameters.Clear();
            SqlConnection con = new SqlConnection();
            int RowCount = -1;
            try
            {
                con.ConnectionString = _conString;
                con.Open();
                PrepareCommand(_command, con, null, strSql, cmdParms);
                RowCount = _command.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return RowCount;
        }
        /// <summary>
        /// 直接SQL语句并返回受影响的行数
        /// </summary>
        public int ExecuteNonQuery(string strSql, List<SqlParameter> paraList)
        {
            return ExecuteNonQuery(strSql, paraList == null ? null : paraList.ToArray());
        }
        #endregion

        #region PrepareCommand
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        #endregion


        #region 事务

        public DataSet Execute(string strSql, SqlTransaction trans)
        {

            DataSet dataSet = null;
            try
            {
                _command.CommandText = strSql;
                _command.Connection = trans.Connection;
                _command.Transaction = trans;
                SqlDataAdapter adapter = new SqlDataAdapter(_command);
                dataSet = new DataSet();
                adapter.Fill(dataSet);


            }
            catch (System.Exception ex)
            {
                dataSet = null;
                throw ex;
            }

            return dataSet;

        }
        public int ExecuteNonQuery(string strSql, SqlTransaction trans)
        {

            int RowCount = -1;
            try
            {
                _command.CommandText = strSql;
                _command.Connection = trans.Connection;
                _command.Transaction = trans;
                RowCount = _command.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {

                throw ex;
            }

            return RowCount;


        }

        #endregion

        #region 属性实现区域

        string _conString = "";
        public string ConnectStr
        {
            get { return _conString; }
            set { _conString = value; }
        }
        //只读属性
        public SqlParameterCollection Parameters
        {
            get { return _command.Parameters; }
        }
        #endregion

    }
    #endregion

}
